package com.wicket.okrcomponent.biz.service.nb;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.wicket.okrcomponent.biz.service.dto.common.BaseMetadataObjectPropertyDto;
import com.wicket.okrcomponent.common.util.CommonFunctionHelper;
import org.apache.skywalking.apm.toolkit.trace.Tag;
import org.apache.skywalking.apm.toolkit.trace.Tags;
import org.apache.skywalking.apm.toolkit.trace.Trace;
import org.springframework.stereotype.Service;
import com.wicket.okrcomponent.biz.service.dto.req.ImplementImportExcelFileDataReqDto;
import com.wicket.okrcomponent.biz.service.dto.res.ImplementImportExcelFileDataRespDto;
import com.wicket.okrcomponent.biz.service.dto.req.ImplementExcelImportReqDto;
import com.wicket.okrcomponent.biz.service.dto.res.ImplementExcelImportRespDto;
import com.wicket.okrcomponent.biz.service.dto.req.ImplementSmartSessionReceiveFieldInputReqDto;
import com.wicket.okrcomponent.biz.service.dto.res.ImplementSmartSessionReceiveFieldInputRespDto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Map;

/**
 * @author ： 手工接入方法
 * @since 2022/5/28 15:32
 *
 *
 * @version 1.0
 */
@Service
public class NbSystemDocking {

    private static Map<String, Object> convertJsonToMap(JSONObject jsonObject) {
        Map<String, Object> map = new HashMap<>();
        for (String key : jsonObject.keySet()) {
            map.put(key, jsonObject.get(key));
        }
        return map;
    }


/**
      * code:dataParsingWarehousing
      * name:M2执行导入excel文件数据（特殊方法）
      * desc:undefined
      * gen by moon at 3/23/2024, 7:29:45 PM
      * 
      **/
     @Trace(operationName = "M2执行导入excel文件数据（特殊方法）")
     @Tags({@Tag(key = "参数",value = "arg[0]"),
     @Tag(key = "返回值",value = "returnedObj")})
      public ImplementImportExcelFileDataRespDto implementImportExcelFileData(ImplementImportExcelFileDataReqDto reqDto) {
         String dynamicTableName = CommonFunctionHelper.humpToUnderline(reqDto.getBusinessObjectCode()); // 表名可能需要进一步转换为符合SQL命名规则的格式
         String url = "jdbc:mysql://" + reqDto.getDatabaseAddress() + ":" + reqDto.getDatabasePort() + "/" + reqDto.getDatabaseName() + "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
         JSONArray jsonArray = JSONArray.parseArray(reqDto.getFileContent());
         Map<String,String> fieldMap = new HashMap<>();
         for(BaseMetadataObjectPropertyDto one : reqDto.getBaseMetadataObjectPropertyList()){
             fieldMap.put(one.getObjectPropertyName(),one.getObjectPropertyCode());
         }
         try (Connection conn = DriverManager.getConnection(url, reqDto.getDatabaseUserId(), reqDto.getDatabasePassWord())) {
             for (int i = 0; i < jsonArray.size(); i++) {
                 JSONObject jsonObject = jsonArray.getJSONObject(i);
                 Map<String, Object> jsonData = convertJsonToMap(jsonObject);
                 // 构建SQL插入语句
                 String sql = "INSERT INTO " + dynamicTableName + " (";
                 StringBuilder fieldsPart = new StringBuilder();
                 StringBuilder valuesPart = new StringBuilder("VALUES (");

                 for (String chineseName : jsonData.keySet()) {
                     String englishName = fieldMap.get(chineseName);
                     fieldsPart.append(CommonFunctionHelper.humpToUnderline(englishName)).append(", ");
                     valuesPart.append("?").append(", ");
                 }

                 // 移除尾部的逗号和空格
                 fieldsPart = new StringBuilder(fieldsPart.substring(0, fieldsPart.length() - 2));
                 valuesPart = new StringBuilder(valuesPart.substring(0, valuesPart.length() - 2));

                 // 完成SQL语句的构建
                 sql += fieldsPart.toString() + ") " + valuesPart.toString() + ")";

                 // 创建PreparedStatement
                 try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                     int index = 1;
                     for (String chineseName : jsonData.keySet()) {
                         pstmt.setObject(index++, jsonData.get(chineseName)); // 使用值
                     }

                     // 执行插入操作
                     int affectedRows = pstmt.executeUpdate();
                     if (affectedRows > 0) {
                         System.out.println("数据插入成功！");
                     } else {
                         System.out.println("数据插入失败！");
                     }
                 }
             }

         } catch (Exception e) {
             e.printStackTrace();
         }
        return new ImplementImportExcelFileDataRespDto();
      }
/**
      * code:excelImport
      * name:M2执行Excel导入（特殊方法）
      * desc:undefined
      * gen by moon at 3/23/2024, 7:30:04 PM
      * 
      **/
     @Trace(operationName = "M2执行Excel导入（特殊方法）")
     @Tags({@Tag(key = "参数",value = "arg[0]"),
     @Tag(key = "返回值",value = "returnedObj")})
      public ImplementExcelImportRespDto implementExcelImport(ImplementExcelImportReqDto reqDto) {
       // TODO ruizhe skai dong ; 3/23/2024, 7:30:04 PM
        return new ImplementExcelImportRespDto();
      }
/**
      * code:receptionService
      * name:M2接收数据库配置出参
      * desc:undefined
      * gen by moon at 3/23/2024, 7:30:17 PM
      * 
      **/
     @Trace(operationName = "M2接收数据库配置出参")
     @Tags({@Tag(key = "参数",value = "arg[0]"),
     @Tag(key = "返回值",value = "returnedObj")})
      public ImplementSmartSessionReceiveFieldInputRespDto implementSmartSessionReceiveFieldInput(ImplementSmartSessionReceiveFieldInputReqDto reqDto) {
         return BeanUtil.toBean(reqDto,ImplementSmartSessionReceiveFieldInputRespDto.class);
      }
  //手工接入方法
}
